﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Xml;
using Common.Utilities.Dispatcher;
using Common.Utilities.OpenXml;

namespace Workflows.Components.Reports.OpenXmlUtil
{
	/// <summary>
	/// using openxml to generate excel report 
	/// </summary>
	public class Excel2007ReportWorker:IWorker 
	{
		#region Implementation of IWorker

		public event WorkerStarted WorkerStartedEvent;
		public event WorkerFinished WorkerFinishedEvent;
		public event WorkerHasError WorkerErredEvent;
		public event WorkerCancelled WorkerCancelledEvent;
		public event WorkerStatusChanged WorkerStatusChangedEvent;

		private Thread workerThread;
		private Excel2007ReportInstruction instruction;
		private Excel2007ReportOutput output;
		private RunContext _Context;
		private Guid _TicketID;
		private string _WorkflowName;
		private string _ComponentName;

		/// <summary>
		/// 
		/// </summary>
		public string ComponentName
		{
			get { return _ComponentName; }
		}

		/// <summary>
		/// 
		/// </summary>
		public string WorkflowName
		{
			get { return _WorkflowName; }
		}

		/// <summary></summary>
		public Thread WorkerThread
		{
			get { return workerThread; }
		}
		/// <summary></summary>
		public InstructionBase Instruction
		{
			get { return instruction; }
		}
		/// <summary></summary>
		public ResultBase Output
		{
			get { return output; }
		}
		/// <summary></summary>
		public Type ComponentType
		{
			get { return typeof(Excel2007ReportComponent); }
		}
		/// <summary></summary>
		public RunContext Context
		{
			get { return _Context; }
			set { _Context = value; }
		}
		/// <summary></summary>
		public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
		{
			this._TicketID = ticketGuid;
			this.instruction = (Excel2007ReportInstruction)request;
			this._WorkflowName = workflowName;
			this._ComponentName = componentName;
		}
		/// <summary></summary>
		public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
		{
			this._TicketID = ticketGuid;
			this.instruction = (Excel2007ReportInstruction)request;
			this._Context = context;
			this._WorkflowName = workflowName;
			this._ComponentName = componentName;
		}
		/// <summary></summary>
		public void DoWork()
		{
			if (this.WorkerStartedEvent != null)
			{
				this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
			}
			this.workerThread = Thread.CurrentThread;
			this.GenerateReport();
		}
		/// <summary></summary>
		public void DoWorkAsync()
		{
			if (this.WorkerStartedEvent != null)
			{
				this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
			}
			this.workerThread = new Thread(new ThreadStart(this.GenerateReport));
			this.workerThread.Start();
		}
		/// <summary></summary>
		public void CancelWork()
		{
			if (this.workerThread != null && this.workerThread.IsAlive)
			{
				this.workerThread.Abort();
				this.workerThread.Join();

				if (this.WorkerCancelledEvent != null)
				{
					this.WorkerCancelledEvent(this._TicketID, this.GetType(), this.instruction);
				}
			}
		}

		#endregion

		#region work
		private void GenerateReport()
		{
			try
			{
				if(File.Exists(this.instruction.ExcelReportFilePath))
					File.Delete(this.instruction.ExcelReportFilePath);

				// grab data 
				if(this.WorkerStatusChangedEvent !=null)
				{
					int pct = 2;
					string msg = string.Format("Grabbing data...");
					this.WorkerStatusChangedEvent(this._TicketID, this.GetType(), this.instruction, this._WorkflowName,
					                              this._ComponentName, msg, pct);
				}
				DataSet ds = this.GrabData();

				// format table for binding 
				if (this.WorkerStatusChangedEvent != null)
				{
					int pct = 50;
					string msg = string.Format("Merging data...");
					this.WorkerStatusChangedEvent(this._TicketID, this.GetType(), this.instruction, this._WorkflowName,
												  this._ComponentName, msg, pct);
				}
				this.LayoutTableBindings(ref ds);

				// write table data to excel file 
				if (this.WorkerStatusChangedEvent != null)
				{
					int pct = 70;
					string msg = string.Format("Generating table reports...");
					this.WorkerStatusChangedEvent(this._TicketID, this.GetType(), this.instruction, this._WorkflowName,
												  this._ComponentName, msg, pct);
				}
				this.WriteTables(this.instruction.ExcelReportFilePath, ds);
				
				// write charts 
				if (this.WorkerStatusChangedEvent != null)
				{
					int pct = 90;
					string msg = string.Format("Generating charts reports...");
					this.WorkerStatusChangedEvent(this._TicketID, this.GetType(), this.instruction, this._WorkflowName,
												  this._ComponentName, msg, pct);
				}
				this.WriteCharts(this.instruction.ExcelReportFilePath, ds, this.instruction.Charts);

				// format worksheets 
				if (this.WorkerStatusChangedEvent != null)
				{
					int pct = 95;
					string msg = string.Format("Formatting worksheet reports...");
					this.WorkerStatusChangedEvent(this._TicketID, this.GetType(), this.instruction, this._WorkflowName,
												  this._ComponentName, msg, pct);
				}
				this.FormatSheets(this.instruction.ExcelReportFilePath, ds);

				this.output = new Excel2007ReportOutput(this.instruction.OwnerComponentID, this._TicketID,
				                                        this.instruction.ExcelReportFilePath);

				if (this.WorkerFinishedEvent != null)
					this.WorkerFinishedEvent(this._TicketID, this.GetType(), this.instruction, this.output);
			}
			catch (Exception ex)
			{
				if (this.WorkerErredEvent != null)
					this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex.ToString(), true);
			}
		}
		#endregion

		#region grab data 
		private DataSet GrabData()
		{
			DataSet dataSet=new DataSet(this._ComponentName);
			
			SqlConnection conn = null;
			foreach (TableDataFromSql provider in this.instruction.TableDataProviders)
			{
				DataSet ds = new DataSet();
				conn = new SqlConnection(provider.ConnStr);
				try
				{
					conn.Open();
					SqlCommand cmd = conn.CreateCommand();
					if(provider.IsStoredProc)
					{
						cmd.CommandType = CommandType.StoredProcedure;
						cmd.Parameters.Clear();
						cmd.CommandText = provider.SelectSql;
						if(provider.Parameters !=null && provider.Parameters.Count>0)
						{
							foreach(string paramName in provider.Parameters.Keys)
							{
								SqlParameter parameter=new SqlParameter("@"+paramName,provider.Parameters[paramName]);
								cmd.Parameters.Add(parameter);
							}
						}
					}
					else
					{
						cmd.CommandType = CommandType.Text;
						if (provider.Parameters != null && provider.Parameters.Count > 0)
						{
							string sql = provider.SelectSql;
							foreach (string paramName in provider.Parameters.Keys)
							{
								sql = sql.Replace("${" + paramName + "}", provider.Parameters[paramName]);
							}
							cmd.CommandText = sql;
						}
						else
						{
							cmd.CommandText = provider.SelectSql;
						}
					}

					SqlDataAdapter daAdapter = new SqlDataAdapter(cmd);
					daAdapter.Fill(ds);

					if(ds.Tables !=null && ds.Tables.Count==1 && provider.OutputTableNames !=null &&
						provider.OutputTableNames.Count==1)
					{
						ds.Tables[0].TableName = provider.OutputTableNames[0];
					}
					if(ds.Tables !=null && ds.Tables.Count>0)
					{
						foreach(DataTable dt in ds.Tables)
						{
							if(string.IsNullOrEmpty(dt.TableName) || dataSet.Tables.Contains(dt.TableName))
							{
								dt.TableName = string.Format("Table_{0}", dataSet.Tables.Count + 1);
							}
							dataSet.Tables.Add(dt.Copy());
						}
					}

					ds.Dispose();
					ds = null;
				}
				catch (Exception ex)
				{
					throw new Exception("Unable to get data: "+ex.ToString());
				}
				finally
				{
					conn.Close();
				}
			}

			return dataSet;
		}
		#endregion

		#region layout 
		/// <summary>
		/// <returns></returns>
		private void LayoutTableBindings(ref DataSet dataSet)
		{
			DataSet dsSheetData = new DataSet(Path.GetFileNameWithoutExtension(this.instruction.ExcelReportFilePath));
			if(dataSet.Tables.Count==1)
			{
				string sheetName = "Sheet 1";
				if(!string.IsNullOrEmpty(dataSet.Tables[0].TableName))
					sheetName = dataSet.Tables[0].TableName;
				else
					dataSet.Tables[0].TableName = sheetName;
				dsSheetData.Tables.Add(dataSet.Tables[0].Copy());
				dataSet = dsSheetData;
				return;
			}
			
			foreach(string sheetName in this.instruction.WorksheetDataBindings.Keys)
			{
				WorksheetTableBinding binding = this.instruction.WorksheetDataBindings[sheetName];
				if(binding.TableNames.Count==1)
				{
					dsSheetData.Tables.Add(dataSet.Tables[binding.TableNames[0]].Copy());
					continue;
				}

				if(binding.ColumnPositions ==null || binding.ColumnPositions.Count==0)
				{
					foreach(DataTable dt in dataSet.Tables)
					{
						if(!binding.TableNames.Contains(dt.TableName))
							continue;
						foreach(DataColumn col in dt.Columns)
						{
							if(!binding.ColumnPositions.ContainsKey(col.ColumnName))
							{
								binding.ColumnPositions.Add(col.ColumnName, binding.ColumnPositions.Count + 1);
							}
						}
					}
				}
				DataTable sheetData=new DataTable(sheetName);
				Dictionary<int,string> columns=new Dictionary<int, string>();
				List<int> colIndexes=new List<int>();
				foreach(string colName in binding.ColumnPositions.Keys)
				{
					columns.Add(binding.ColumnPositions[colName],colName);
					colIndexes.Add(binding.ColumnPositions[colName]);
				}
				colIndexes.Sort();
				foreach(int colIdx in colIndexes)
				{
					sheetData.Columns.Add(columns[colIdx]);
				}

				// primary key: find ptable and pkey
				string pkTableName = string.Empty;
				string pkFieldName = string.Empty;
				if(binding.TableKeyFields.Count>0)
				{
					string[] pkTbls=new string[binding.TableKeyFields.Count];
					binding.TableKeyFields.Keys.CopyTo(pkTbls,0);
					pkTableName = pkTbls[0];
					pkFieldName = binding.TableKeyFields[pkTableName];
				}
				if(string.IsNullOrEmpty(pkFieldName))
				{
					pkTableName = dataSet.Tables[0].TableName;
					pkFieldName = dataSet.Tables[0].Columns[0].ColumnName;
				}

				// add ptable data 
				foreach(DataRow dr in dataSet.Tables[pkTableName].Rows)
				{
					DataRow drNew = sheetData.NewRow();
					foreach(DataColumn col in dataSet.Tables[pkTableName].Columns)
					{
						if(sheetData.Columns.Contains(col.ColumnName))
						{
							drNew[col.ColumnName] = dr[col.ColumnName];
						}
					}
					sheetData.Rows.Add(drNew);
				}

				// add key first
				foreach(string tblName in binding.TableNames)
				{
					if(!binding.TableNames.Contains(tblName))
						continue;

					if(tblName !=pkTableName && binding.TableKeyFields !=null && binding.TableKeyFields.ContainsKey(tblName))
					{
						string fkFieldName = binding.TableKeyFields[tblName];
						List<string> processedKeyValues=new List<string>();
						foreach(DataRow dr in dataSet.Tables[tblName].Rows)
						{
							string keyValue = dr[fkFieldName].ToString();
							if (processedKeyValues.Contains(keyValue))
								continue;

							DataRow[] dataRows = dataSet.Tables[tblName].Select(string.Format("{0}={1}", fkFieldName, keyValue));
							DataRow[] sheetRows = sheetData.Select(string.Format("{0}={1}", fkFieldName, keyValue));
							if(sheetRows==null || sheetRows.Length==0)
								continue;

							if(dataRows.Length==1 && sheetRows.Length==1)
							{
								foreach(DataColumn col in dataSet.Tables[tblName].Columns)
								{
									if(sheetData.Columns.Contains(col.ColumnName) && 
										sheetRows[0][col.ColumnName]==null)
									{
										sheetRows[0][col.ColumnName] = dataRows[0][col.ColumnName];
									}
								}
							}
							else if(dataRows.Length>1 && sheetRows.Length==1)
							{
								foreach (DataColumn col in dataSet.Tables[tblName].Columns)
								{
									if (sheetData.Columns.Contains(col.ColumnName) &&
										sheetRows[0][col.ColumnName] == null)
									{
										sheetRows[0][col.ColumnName] = dataRows[0][col.ColumnName];
									}
								}
								for(int i=1;i<dataRows.Length;i++)
								{
									DataRow newSheetRow = sheetData.NewRow();
									foreach(DataColumn col in dataSet.Tables[pkTableName].Columns)
									{
										newSheetRow[col.ColumnName] = sheetRows[0][col.ColumnName];
									}
									foreach(DataColumn col in dataSet.Tables[tblName].Columns)
									{
										if(sheetData.Columns.Contains(col.ColumnName) && 
											newSheetRow[col.ColumnName]==null)
										{
											newSheetRow[col.ColumnName] = dataRows[0][col.ColumnName];
										}
									}
									sheetData.Rows.Add(newSheetRow);
								}
							}
							else if(dataRows.Length>1 && sheetRows.Length>1)
							{
								// not implemented	
							}
							processedKeyValues.Add(keyValue);
						}
					}
				}

				dsSheetData.Tables.Add(sheetData);
			}

			dataSet = dsSheetData;
		}
		#endregion

		#region write table
		private void WriteTables(string excelFilePath, DataSet dsSheetData)
		{
			ExcelWriter.CreateWorkbook(excelFilePath, dsSheetData);
		}
		#endregion

		#region write chart
		private void WriteCharts(string excelFilePath, DataSet dsSheetData, Dictionary<string, ChartDefinition> chartDefinitions)
		{
			int drawingID = 0;
			foreach(string chartName in chartDefinitions.Keys)
			{
				drawingID++;
				ExcelWriter.CreateChart(excelFilePath, dsSheetData, drawingID, chartDefinitions[chartName]);
			}
		}
		#endregion

		#region format table 
		private void FormatSheets(string excelReportFilePath, DataSet dsSheetData)
		{
			int tableID = 0;
			foreach(string sheetName in this.instruction.WorksheetDataBindings.Keys)
			{
				tableID++;
				DataTable dtSheetData = dsSheetData.Tables[sheetName];
				ExcelWriter.FormatWorksheetTable(excelReportFilePath, sheetName, dtSheetData, tableID);
			}
		}
		#endregion
	}


}
